import os
import xlrd
from xlutils.copy import copy


def base_dir(filename=None):
    return os.path.join(os.path.dirname(__file__), filename)


str1 = "this is string example....wow!!! this is really string";
print(str1.replace("is", "was"))
print(str1.replace("is", "was", 3))

"""对excel进行操作"""
work = xlrd.open_workbook(base_dir("excel.xlsx"))
# 索引到第X个工作表
sheet = work.sheet_by_index(0)
# 查看有多少行
print(sheet.nrows)
# 查看有多少列
print(sheet.ncols)
# 获取单元格内容


# for i in range(1730):
#     print(i, sheet.cell_value(i, 0))
# value = sheet.cell_value(i, 0)
# if ("专业" in value):
#     sheet.write(7, 2, "Tao")
# value.relace("专业", "DONE")

# print(i, sheet.cell_value(i, 0))
"""对excel进行修改/添加内容"""

# 找到需要更该的xls
work = xlrd.open_workbook(base_dir("excel.xlsx"))
print(work)
# 对数据表格进行复制
old_content = copy(work)
# 定位到Sheet1表
ws = old_content.get_sheet(0)
# 在sheet1表中写入内容

list = ("专业", "")


def setValue(ww, sss, i, sheetValue3, sheetValue4, sheetValue5, sheetValue6, sheetValue7, sheetValue8, sheetValue9,
             sheetValue10):
    if (len(sheetValue3) == 0):
        ww.write(i, 3, sss)
    elif (len(sheetValue4) == 0):
        ww.write(i, 4, sss)
    elif (len(sheetValue5) == 0):
        ww.write(i, 5, sss)
    elif (len(sheetValue6) == 0):
        ww.write(i, 6, sss)
    elif (len(sheetValue7) == 0):
        ww.write(i, 7, sss)
    elif (len(sheetValue8) == 0):
        ww.write(i, 8, sss)
    elif (len(sheetValue9) == 0):
        ww.write(i, 9, sss)
    elif (len(sheetValue10) == 0):
        ww.write(i, 10, sss)


for i in range(1730):
    # try:
    #     sheetValue3 = sheet.cell_value(i, 3)
    #     sheetValue4 = sheet.cell_value(i, 4)
    #     sheetValue5 = sheet.cell_value(i, 5)
    #     sheetValue6 = sheet.cell_value(i, 6)
    #     sheetValue7 = sheet.cell_value(i, 7)
    #     sheetValue8 = sheet.cell_value(i, 8)
    #     sheetValue9 = sheet.cell_value(i, 9)
    #     sheetValue10 = sheet.cell_value(i, 10)
    # except IndexError:
    #     pass

    value = sheet.cell_value(i, 0)
    if ("专业" in value):
        aaa = str(value)
        # aaa.relace("专业", "DONE")
        # ws.write(i, 0, aaa)
        ws.write(i, 3, "专业")
        # setValue(ws, "专业", i, sheetValue3, sheetValue4, sheetValue5, sheetValue6, sheetValue7, sheetValue8, sheetValue9,
        #          sheetValue10)

    if ("高大上" in value):
        # setValue(ws, "高大上", i, sheetValue3, sheetValue4, sheetValue5, sheetValue6, sheetValue7, sheetValue8,
        #          sheetValue9,
        #          sheetValue10)
        ccc = 1
        try:
            value1 = sheet.cell_value(i, 3)
        except IndexError:
            ccc = 0
        if (ccc == 0):
            ws.write(i, 3, "高大上")
        else:
            ws.write(i, 4, "高大上")

# 对修改后的内容进行保存
old_content.save(base_dir("data.xls"))
